package cn.chendd.example.jpa.user.repository;

import cn.chendd.example.jpa.nativequery.annotions.Query;
import cn.chendd.example.jpa.nativequery.annotions.QueryResult;
import cn.chendd.example.jpa.user.dao.NaiveQueryForBeanDao;
import cn.chendd.example.jpa.user.dto.UserResultDto;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author chendd
 * @date 2020/5/11 22:36
 */
public interface BeanResultSqlQuery {

    @Query(sql = "select 88911 id , 'chendd' userName , now() createTime , 88911.006 amount , " +
            "            3.1415926 pi , 1 status", entityManager = NaiveQueryForBeanDao.class)
    UserResultDto queryForBean();

    @Query(sql = "select 88911 id , 'chendd' userName , now() createTime , 88911.006 amount , " +
            " 3.1415926 pi , 1 status" , entityManager = NaiveQueryForBeanDao.class)
    List<UserResultDto> queryForList();

    @Query(sql = "select * from (" +
        "select 1 id , 'chen11' userName , now() createTime " +
         " union all " +
         "select 2 id , 'chen22' userName , now() createTime " +
         " union all " +
         "select 3 id , 'chen33' userName , now() createTime " +
         " union all " +
         "select 4 id , 'chen44' userName , now() createTime " +
         ") newTab" , entityManager = NaiveQueryForBeanDao.class
         , resultSet = @QueryResult(include = {"id" , "userName" , "createTime"}))
    List<UserResultDto> queryForListForPart();

    @Query(sql = "select * from (" +
        "select 1 id , 'chen11' userName , now() createTime " +
         " union all " +
         "select 2 id , 'chen22' userName , now() createTime " +
         " union all " +
         "select 3 id , 'chen33' userName , now() createTime " +
         " union all " +
         "select 4 id , 'chen44' userName , now() createTime " +
         ") newTab where id > :id and (:name is null or userName like concat(:name,'%'))"
         , entityManager = NaiveQueryForBeanDao.class
         , resultSet = @QueryResult(include = {"id" , "userName" , "createTime"}))
    List<UserResultDto> queryForListForParam(@Param("id") Long userId , @Param("name") String userName);

    @Query(sql = "select a.name userName from ${value(spring.jpa.properties.hibernate.default_catalog) , after(.)}user a " +
            " where 1=1 limit :limit"
            , entityManager = NaiveQueryForBeanDao.class
            , resultSet = @QueryResult(include = "userName")
    )
    List<UserResultDto> queryForListForVariableParam(@Param("limit") Long limit);

}
